/*
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.    
*/


/* Name
' DataHandling
' Matthew Tuffin 23/08/2007
' Desc
 * Handles helper functions for DataSets, DataTables etc
'
' Requirements:
 * System.Data 
 * System.Collections
 * System.Windows.Forms
 * System.Data.SqlClient
 * System.Runtime.Serialization
 * System.IO
 * Logger
'
' Usage:
 * Static Class Functions. Use with the DataHandling. etc.
'
' Limitations:
 * 
'
*/

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using Logger;
using System.Text.RegularExpressions;

namespace DBConnection
{
    /// <summary>
    /// DataHandling class for DataSets,DataTables etc.
    /// </summary>
    public class DataHandling
    {
       
        /// <summary>
        /// Delegate for updating a row
        /// </summary>
        /// <param name="row">Datarow</param>
        public delegate void UpdateRowData(DataRow row);

        /// <summary>
        /// Creates a new DataSet Object. Forces a locale of Australia
        /// </summary>
        /// <param name="strName">DataSet Name</param>
        /// <returns></returns>
        public static DataSet RequestNewDataSet(string strName)
        {
            DataSet DS = new DataSet(strName);
            DS.Locale = new System.Globalization.CultureInfo("en-AU");
            DS.Disposed += new EventHandler(DS_Disposed);
            return DS;            
        }
        
        /// <summary>
        /// Event for disposing a datasets contents
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        static void DS_Disposed(object sender, EventArgs e)
        {
            DataSet DS = (DataSet)sender;
            foreach (DataTable DT in DS.Tables)
            {
                foreach (DataColumn DC in DT.Columns)
                {
                    DC.Dispose();
                }
                DT.Dispose();
            }
        }

        /// <summary>
        /// Create a new row
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="ColumnData">ColumnData</param>
        /// <param name="bDoNotMarkRowAsUpdated">Do not mark row as updated</param>
        /// <returns>DataRow</returns>
        public static DataRow NewRow(ref DataTable DT, object[] ColumnData, bool bDoNotMarkRowAsUpdated)
        {
            return (DT.LoadDataRow(ColumnData, bDoNotMarkRowAsUpdated));
        }

        /// <summary>
        /// Create a new column
        /// </summary>
        /// <param name="strColumnName">ColumnName</param>
        /// <param name="ColumnType">ColumnType</param>
        /// <param name="bDBAllowed">NullAllowed</param>
        /// <param name="bReadOnly">Readonly field</param>
        /// <param name="iMaxLength">MaxLength</param>
        /// <param name="bUnique">Unique field</param>
        /// <param name="strExp">Expression</param>
        /// <param name="bAutoIncrement">Autoincrement on field</param>
        /// <returns>DataColumn</returns>
        public static DataColumn NewColumn(string strColumnName, Type ColumnType,
                                               bool bDBAllowed, bool bReadOnly,
                                               int iMaxLength, bool bUnique, 
                                               string strExp, bool bAutoIncrement)
        {
            DataColumn newColumn = new DataColumn(strColumnName, ColumnType);

            newColumn.AllowDBNull = bDBAllowed;
            newColumn.ReadOnly = bReadOnly;
            newColumn.MaxLength = iMaxLength;
            newColumn.Unique = bUnique;
            newColumn.Expression = strExp;

            if (bAutoIncrement)
            {
                newColumn.AutoIncrement = true;
                newColumn.AutoIncrementSeed = -1;
                newColumn.AutoIncrementStep = -1;
            }

            return (newColumn);
        }
        
        /// <summary>
        /// Define a unique or foreign constrint
        /// Pass false and null to col2 to define a unique otherwise fill them for a foriegn
        /// </summary>
        /// <param name="strName">Name</param>
        /// <param name="col">DataColumn</param>
        /// <param name="bUsedAsPrimaryKey">Use column as primay key?</param>
        /// <param name="bForeign">Is Key Foriegn?</param>
        /// <param name="col2">Add foreign column</param>
        /// <returns>Constraint</returns>
        public static Constraint DefineAConstraint(string strName, DataColumn col, bool bUsedAsPrimaryKey, 
                                             bool bForeign, DataColumn col2)
        {
            if (bForeign)
            {
                ForeignKeyConstraint FKey = new ForeignKeyConstraint(strName, col, col2);
                return (FKey);
            }
            else
            {
                UniqueConstraint Unique = new UniqueConstraint(strName, col, bUsedAsPrimaryKey);
                return (Unique);
            }           
        }

        /// <summary>
        /// Update a row. Use this to track and undo changes made on one row
        /// </summary>
        /// <param name="row">DataRow to update</param>
        /// <param name="RowUpdate">Function pointer to "void Function(DataRow row)"</param>
        /// <returns>return if succesful</returns>
        public static bool UpdateRow(DataRow row, UpdateRowData RowUpdate)
        {
            if (row == null)
            {
                CMsgBox.ShowMsgBox("Row is null can not update row in DataSet");                
                return (false);
            }

            try
            {
                row.BeginEdit();
                    RowUpdate(row);
                row.EndEdit();
            }
            catch (Exception ex)
            {
                row.CancelEdit();
                CMsgBox.ShowMsgBox(ex.Message);                
                ErrorLog.HandleException(ex);
                
                return (false);
            }

            return (true);
        }

        /// <summary>
        /// Remove a row from a table
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="row">DataRow</param>
        public static void RemoveRow(ref DataTable DT, DataRow row)
        {
            try
            {                
                DT.Rows.Remove(row);
            }
            catch (Exception ex)
            {                
                CMsgBox.ShowMsgBox("Row has not been marked as removed from table");
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
            }
        }

        /// <summary>
        /// Seralise a DataSet into a binary file
        /// </summary>
        /// <param name="dsInput">DataSet to seralise</param>
        /// <param name="strFileName">Filename and path</param>
        public static void SerializeDataSetBinary(DataSet dsInput, string strFileName)
        {
            try
            {
                string strTempFile = System.IO.Directory.GetCurrentDirectory() + "\\" + strFileName;

                using (FileStream stream = new FileStream(strTempFile, FileMode.Create))
                {
                    IFormatter formatter = new BinaryFormatter();
                    formatter.Serialize(stream, dsInput);

                    stream.Flush();
                    stream.Position = 0;
                }
            }
            catch (Exception ex)
            {                
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
            }
        }

        /// <summary>
        /// Writes the contents of a table that has failed to update properly
        /// </summary>
        /// <param name="DT">DataTable Write Out</param>
        /// <param name="strTableName">DataTable Name</param>
        public static void WriteRecoveryTable(ref DataTable DT, string strTableName)
        {
            string strTempFile = "TableDumps\\" + DateTime.Now.ToString().Replace("/","-").Replace(":","~") + " " + strTableName + ".xml";
            WriteXMLForTable(ref DT, strTempFile, XmlWriteMode.WriteSchema);
        }

        /// <summary>
        /// Writes XML for table whether it be data or just schema
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="strFileName">Filename</param>
        /// <param name="WriteMode">Type of XML to write XmlWriteMode enum</param>
        public static void WriteXMLForTable(ref DataTable DT, string strFileName, XmlWriteMode WriteMode)
        {
            try
            {
                string strTempFile = System.IO.Directory.GetCurrentDirectory() + "\\" + strFileName;

                using (FileStream stream = new FileStream(strTempFile, FileMode.Create))
                {
                    DT.WriteXml(stream, WriteMode, false);
                }
            }
            catch (Exception ex)
            {
                CMsgBox.ShowMsgBox(ex.Message); 
                ErrorLog.HandleException(ex);
            }
        }

        /// <summary>
        /// Writes XML schema for a Table. Useful in constructing a xsd files to construct strongly typed datasets
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="strFileName">Filename</param>
        public static void WriteXMLSchemaForTable(ref DataTable DT, string strFileName)
        {
            try
            {
                string strTempFile = System.IO.Directory.GetCurrentDirectory() + "\\" + strFileName;

                using (FileStream stream = new FileStream(strTempFile, FileMode.Create))
                {
                    DT.WriteXmlSchema(stream);
                }
            }
            catch (Exception ex)
            {                
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
            }
        }

        /// <summary>
        /// Accept changes made to a table. This is for temp changes to table and will not update the database
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="bAcceptChanges">Accept all the recent changes or reject</param>
        public static void AcceptChangesToTable(ref DataTable DT, bool bAcceptChanges)
        {            
            if (bAcceptChanges)
            {
                DT.AcceptChanges();
            }
            else
            {
                DT.RejectChanges();
            }
        }
        
        /// <summary>
        /// Generates an xml file of all the recent changes made to a datatable
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="strFileName">Filename</param>
        
        public static void XMLSnapShotofChangesMadeToTable(ref DataTable DT, string strFileName)
        {
            DataTable DTChanges = DT.GetChanges();
            WriteXMLForTable(ref DTChanges, strFileName, XmlWriteMode.IgnoreSchema);
        }

        /// <summary>
        /// New Data Relationship
        /// </summary>
        /// <param name="strName">Name</param>
        /// <param name="parentCol">parentColumn</param>
        /// <param name="childCol">childColumn</param>
        /// <param name="bContrainstsOn">Constrinst Active</param>
        /// <returns>DataRelation</returns>
        public static DataRelation NewDataRelation(string strName, DataColumn parentCol, DataColumn childCol, bool bContrainstsOn)
        {
            DataRelation DR = new DataRelation(strName, parentCol, childCol,bContrainstsOn);            
            return (DR);
        }

        /// <summary>
        /// Obtain a list of child rows associated through a relationship
        /// </summary>
        /// <param name="Row">Row</param>
        /// <param name="rel">DataRelation</param>
        /// <returns>DataRow Array</returns>
        public static DataRow[] ObtainChildRowsThroughRelation(DataRow Row, DataRelation rel)
        {
            try
            {
                return (Row.GetChildRows(rel));
            }
            catch (Exception ex)
            {                
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
                return null;
            }
        }

        /// <summary>
        /// Obtain the parent row associated through a relationship
        /// </summary>
        /// <param name="Row">DataRow</param>
        /// <param name="rel">DataRelation</param>
        /// <returns>DataRow</returns>
        public static DataRow ObtainParentRowThroughRelation(DataRow Row, DataRelation rel)
        {
            try
            {
                return (Row.GetParentRow(rel));
            }
            catch (Exception ex)
            {                
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
                return null;
            }
        }

        /// <summary>
        /// Replaces quotes in a string with double quotes
        /// </summary>
        /// <param name="strQuotedstring"></param>
        /// <returns></returns>
        public static string StringWithQuotesForDatabases(string strQuotedstring)
        {
            return (strQuotedstring.Replace("'", "''"));
        }

        /// <summary>
        /// Using a filter and sort returns datarows[]
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="strFilter">Filter</param>
        /// <param name="strSort">SortFilter</param>
        /// <returns>DataRow Array</returns>
        public static DataRow[] FindAndSort(ref DataTable DT, string strFilter, string strSort)
        {
            return (DT.Select(strFilter, strSort));
        }

        /// <summary>
        /// Creates a new DataView
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="strRowFilter">Filter</param>
        /// <param name="strSort">Sort</param>
        /// <param name="RowState">What state of the Row should we look at? DataViewRowState enum</param>
        /// <returns>DataView</returns>
        public static DataView RequestNewDataView(ref DataTable DT, string strRowFilter,
                                                 string strSort, DataViewRowState RowState)
        {
            DataView DV = new DataView(DT, strRowFilter, strSort, RowState);
            return (DV);
        }

        /// <summary>
        /// Remove Columns from an insert statement
        /// </summary>
        /// <param name="strColumns">Columns to be removed</param>
        /// <param name="DataSource">DataSource Object</param>
        /// <param name="strQueryName">QueryName to pull from</param>
        /// <param name="bInsert">Is this an insert or update?</param>
        public static void RemoveColumnsFromStatement(string[] strColumns, ref CDataSource DataSource, string strQueryName, bool bInsert)
        {
            foreach (string strColumn in strColumns)
            {
                if (bInsert)
                {
                    DataSource.DataBaseConnection.GetAdapterForGivenTableName(strQueryName).InsertCommand.CommandText
                                               = RemoveColumnFromInsertStatement
                                               (DataSource.DataBaseConnection.GetAdapterForGivenTableName(strQueryName).InsertCommand.CommandText, strColumn);
                }
                else
                {
                    DataSource.DataBaseConnection.GetAdapterForGivenTableName(strQueryName).UpdateCommand.CommandText
                                                       = RemoveColumnFromUpdateStatement
                                                (DataSource.DataBaseConnection.GetAdapterForGivenTableName(strQueryName).UpdateCommand.CommandText, strColumn);
                }
            }
        }


        private static string RemoveColumnFromInsertStatement(string strInsertSQL, string strFieldName)
        {
            string strOutput = strInsertSQL.Replace("@" + strFieldName + ", ", "");
            strOutput = strOutput.Replace(strFieldName + ", ", "");
            return (strOutput);
        }


        private static string RemoveColumnFromUpdateStatement(string strInsertSQL, string strFieldName)
        {
            string strOutput = strInsertSQL.Replace(strFieldName + " = @" + strFieldName + ", ", "");
            return (strOutput);
        }


        private static bool ColumnEqual(object A, object B)
        {
            // Compares two values to see if they are equal. Also compares DBNULL.Value.
            // Note: If your DataTable contains object fields, then you must extend this
            // function to handle them in a meaningful way if you intend to group on them.

            if (A == DBNull.Value && B == DBNull.Value) //  both are DBNull.Value
                return true;
            if (A == DBNull.Value || B == DBNull.Value) //  only one is DBNull.Value
                return false;
            return (A.Equals(B));  // value type standard comparison
        }

        /// <summary>
        /// Select Distinct on One Field
        /// </summary>
        /// <param name="TableName">New Table Name</param>
        /// <param name="SourceTable">The source table to use</param>
        /// <param name="FieldName">Field name to distinct on</param>
        /// <returns>The resulting datatable</returns>
        public static DataTable SelectDistinctOnOneField(string TableName, DataTable SourceTable, string FieldName)
        {
            DataTable dt = new DataTable(TableName);
            dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

            object LastValue = null;
            foreach (DataRow dr in SourceTable.Select("", FieldName))
            {
                if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
                {
                    LastValue = dr[FieldName];
                    dt.Rows.Add(new object[] { LastValue });
                }
            }
            return dt;
        }

        /// <summary>
        /// Select Distinct on a number of columns
        /// </summary>
        /// <param name="SourceTable">SourceTable</param>
        /// <param name="Columns">Columns to distinct on</param>
        /// <returns>Datatable</returns>
        public static  DataTable SelectDistinctOnMultipleFields(DataTable SourceTable, params string[] Columns)
        {
            DataTable Result = new DataTable();
            if (SourceTable != null)
            {
                DataView DView = SourceTable.DefaultView;
                try
                {
                    Result = DView.ToTable(true, Columns);
                }
                catch (Exception)
                {

                }
            }
            return Result;
        }

        /// <summary>
        /// Left join On two datatables returning the resulting table
        /// </summary>
        /// <param name="DS">The dataSet the two datatables are in. Datatables must both exist in the Dataset</param>
        /// <param name="strRelName">A name given to the realtionship between the tables</param>
        /// <param name="parentColumn">The parent column on the first table to use in realtionship</param>
        /// <param name="childColumn">The child column on the second table to use in realtionship</param>
        /// <param name="DTMaster">The parent table used to copy the fields directly from it</param>
        /// <param name="ColumnsToAdd">A list of columns to add to the final resulting table</param>
        /// <returns>The result of join</returns>
        public static DataTable LeftJoinTables(ref DataSet DS, string strRelName,
                DataColumn parentColumn, DataColumn childColumn, DataTable DTMaster, CColumnInfo[] ColumnsToAdd)
        {
            DataRelation rel =
                new DataRelation(strRelName, parentColumn, childColumn, false);

            DS.Relations.Add(rel);

            DataTable DTResults = DTMaster.Clone();

            foreach (CColumnInfo ColumnInfo in ColumnsToAdd)
            {
                DTResults.Columns.Add(new DataColumn(ColumnInfo.m_strNewFieldName));
            }

            foreach (DataRow RowFdoc in DTMaster.Rows)
            {
                DataRow Row = DTResults.LoadDataRow(RowFdoc.ItemArray, true);

                foreach (DataRow RowRelation in RowFdoc.GetChildRows(rel))
                {
                    foreach (CColumnInfo ColumnInfo in ColumnsToAdd)
                    {
                        Row[ColumnInfo.m_strNewFieldName] = RowRelation[ColumnInfo.m_strOriginalFieldName];
                    }
                }
            }
            return DTResults;
        }

        /// <summary>
        /// Checks if a string is all letters
        /// </summary>
        /// <param name="strToCheck">The string to compare</param>
        /// <returns>true if it is all letters</returns>
        public static bool IsAlpha(string strToCheck)
        {
            Regex objAlphaPattern = new Regex("[^a-zA-Z]");

            return !objAlphaPattern.IsMatch(strToCheck);
        }


       
    }
}
